BEGIN;

CREATE TABLE user (
	id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
	object_state VARCHAR(30) NOT NULL,
	sid VARCHAR(32) NOT NULL,
	first_name VARCHAR(25) NOT NULL,
	last_name VARCHAR(35) NOT NULL,
	login VARCHAR(15) NOT NULL,
	password VARCHAR(65) NOT NULL,
	email VARCHAR(30) NOT NULL,
	UNIQUE (sid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE school_type (
	id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
	object_state VARCHAR(30) NOT NULL,
	sid VARCHAR(32) NOT NULL,
	name VARCHAR(25) NOT NULL,
	user_id INT(11) NOT NULL,
	FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE,
	UNIQUE (sid),
	UNIQUE (name, user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE school (
	id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
	object_state VARCHAR(30) NOT NULL,
	sid VARCHAR(32) NOT NULL,
	name VARCHAR(50) NOT NULL,
	type_id INT(11) NOT NULL,
	user_id INT(11) NOT NULL,
	street VARCHAR(35) NOT NULL,
	zip VARCHAR(6) NOT NULL,
	city VARCHAR(30) NOT NULL,
	FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE,
	FOREIGN KEY (type_id) REFERENCES school_type(id) ON DELETE NO ACTION,
	UNIQUE (sid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE contact_person {
    id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
	object_state VARCHAR(30) NOT NULL,
	sid VARCHAR(32) NOT NULL,
	first_name VARCHAR(25) NOT NULL,
	last_name VARCHAR(35) NOT NULL,
	phone VARCHAR(9),
	email VARCHAR(35),
	school_id INT(11) NOT NULL,
	FOREIGN KEY (school_id) REFERENCES school(id) ON DELETE CASCADE,
	UNIQUE (sid)
} ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE room (
    id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
	object_state VARCHAR(30) NOT NULL,
	sid VARCHAR(32) NOT NULL,
	name VARCHAR(50) NOT NULL,
    street VARCHAR(35) NOT NULL,
	zip VARCHAR(6) NOT NULL,
	city VARCHAR(30) NOT NULL,
	capacity INT(11),
	lease_price DOUBLE,
	contact_first_name VARCHAR(25),
	contact_last_name VARCHAR(35),
	phone VARCHAR(9),
	mobile VARCHAR(9),
	email VARCHAR(35),
	tax_id VARCHAR(10),
	user_id INT(11) NOT NULL,
	column_count INT(11) NOT NULL,
	row_count INT(11) NOT NULL,
	FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE,
	UNIQUE (sid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE room_place (
    id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
	object_state VARCHAR(30) NOT NULL,
	sid VARCHAR(32) NOT NULL,
	room_id INT(11) NOT NULL,
	row_number INT(11) NOT NULL,
	column_number INT(11) NOT NULL,
	type VARCHAR(30) NOT NULL,
	FOREIGN KEY (room_id) REFERENCES room(id) ON DELETE CASCADE,
	UNIQUE (sid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE event (
    id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
	object_state VARCHAR(30) NOT NULL,
	sid VARCHAR(32) NOT NULL,
	name VARCHAR(50) NOT NULL,
	room_id INT(11) NOT NULL,
	date_time TIMESTAMP NOT NULL,
	description TEXT,
	price DOUBLE,
	FOREIGN KEY (room_id) REFERENCES room(id) ON DELETE CASCADE,
	UNIQUE (sid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

COMMIT;